import pymysql
class DbTools(object):
    # 建立连接
    @staticmethod
    def __create_conn():
        # 指定ip、port、用户名、密码、数据库、返回连接
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                               password="iHRM_student_2022", database="ihrm", charset="utf8")
        return conn
    @staticmethod
    def query_one(sql):
        # 指定ip、port、用户名、密码、数据库、返回一条结果
        conn = None
        cursor = None
        res = None
        try:
            conn = DbTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as e:
            print("查询sql语句失败：",e)
        finally:
            cursor.close()
            conn.close()
            return res

    @staticmethod
    def query_many(sql,n):
        # 指定ip、port、用户名、密码、数据库、返回多条结果
        conn = None
        cursor = None
        res = None
        try:
            conn = DbTools.__create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchmany(n)
        except Exception as e:
            print("查询多条结果：",e)
        finally:
            cursor.close()
            conn.close()
            return res



        pass
    def query_all(self,sql):
        # 指定ip、port、用户名、密码、数据库、返回全部结果
        pass
    @staticmethod
    def db_idu(sql):
        conn = None
        cursor = None
        try:
            conn = DbTools.__create_conn()
            cursor = conn.cursor()
            res = cursor.execute(sql)
            print("Affected rows:",res)
            conn.commit()
        except Exception as e:
            conn.rollback()
            print("增删改sql失败：", e)
        finally:
            # 6. 关闭连接
            conn.close()
        # 获取连接、创建游标、执行SQL、提交/回滚事务




if __name__ == '__main__':
    res = DbTools.query_one("select * from t_book where id=2;")
    print("查一条",res)

    res1 = DbTools.query_many("select * from t_book;",3)
    print("查多条", res1)

    DbTools.db_idu("INSERT into t_book(id,title,pub_date) values(4,'嘿哈哟','1986-01-01');")

    DbTools.db_idu("UPDATE t_book  set `read`= `READ` +1 where title='嘿哈哟';")